<?php

namespace App\Services;

use App\Models\Certificate;
use Illuminate\Support\Facades\DB;

class CertificateService extends  BaseService
{
    /**
     * 获取列表
     * @return
     */
    public function list($page,$limit,$sort,$order,$name,$certificate_category_id) {
        // $query = Certificate::leftJoin('certificate_categories', 'certificate_categories.id', '=', 'certificates.certificate_category_id')
        //     ->select('certificates.*',
        //         'certificate_categories.name as categoriesName'
        //     );;
        // if(!empty($name)){
        //     $query->where('certificates.name','like',"%$name%");
        // }
        // if(!empty($certificate_category_id)){
        //     $query->where('certificate_category_id',$certificate_category_id);
        // }
        // return $query->orderBy($sort,$order)->paginate($limit, ['*'], 'page', $page);
        $query = Certificate::leftJoin('certificate_categories', 'certificate_categories.id', '=', 'certificates.certificate_category_id')
            ->leftJoin('exam_papers', function ($join) {
                // 连接试卷表，并筛选出 is_published = 1 的试卷
                $join->on('exam_papers.certificate_id', '=', 'certificates.id')
                    ->where('exam_papers.is_published', 1);
            })
            ->select('certificates.*',
                'certificate_categories.name as categoriesName', // 获取证书分类名称
                DB::raw('IFNULL(MAX(exam_papers.created_at), 0) as lastExamPaperCreatedAt'), // 获取最后一个试卷的创建时间，如果没有试卷则返回 0
                DB::raw('IF(COUNT(exam_papers.id) > 0, 1, 0) as hasExamPaper') // 检查是否存在试卷，返回 1 或 0
            );
        if(!empty($name)){
            $query->where('certificates.name','like',"%$name%");
        }
        if(!empty($certificate_category_id)){
            $query->where('certificate_category_id',$certificate_category_id);
        }
        $query->groupBy('certificates.id'); // 按证书 ID 分组
        return $query->orderBy($sort,$order)->paginate($limit, ['*'], 'page', $page);
    }
}
